﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WinUI {
    public class PostgreSqlJavaGenerator {
        public static string GenJavaName(string dbName) {
            // e.g: is_competitor --> IsCompetitor

            string[] strs = dbName.Split(new string[] { "_" }, StringSplitOptions.RemoveEmptyEntries);

            string name = "";
            foreach (string str in strs) {
                string newStr = str.Substring(0, 1).ToUpper() + str.Substring(1);
                name += newStr;
            }

            return name;
        }

        #region model
        public static string GenModelJava(PostgreSqlTable table) {
            string javaName = GenJavaName(table.Name);
            string errMsgs = GenModelErrMsgs(table);
            string variables = GenModelVariablesJava(table);
            string getset = GenModelGetSet(table);
            string readResultSet = GenModelReadResultSet(table);
            string dbName = table.Name;

            string selectParams = GenModelSelectParam(table);
            string setSelectParams = GenModelSetSelectParam(table);

            string selectListParams = GenModelSelectListParam(table);
            string setSelectListParams = GenModelSetSelectListParam(table);

            string insertParams = GenModelInsertParam(table);
            string setInsertParams = GenModelSetInsertParams(table);

            string updateParams = GenModelUpdateParams(table);
            string setUpdateParams = GenModelSetUpdateParams(table);

            string primaryKeys = GenModelPrimaryKeys(table);

            string fillTemplateDBName = GenModelFillTemplateDbName(table);
            string fillTemplateJavaName = GenModelFillTemplateJavaName(table);

            string fieldNames = GenModelFieldNames(table);

            #region JAVA

            string java = string.Format(@"package model;

import java.sql.*;
import java.util.Date;
import dal.*;
import util.*;

public class {0}Model implements IModel {{

	public static class FieldNames {{
{15}	
	}}

	public class ErrorMessages {{
{1}	
	}}

    public ErrorMessages errMsgs = new ErrorMessages();

	public String[] errMsgList = null;

// private variables
{2}

	public {0}Model() {{
	}}

// get/set
{3}

	@Override
	public int[] getPrimaryKey() {{
		int[] ids = new int[1];
		ids[0] = getId();
		return ids;
	}}

	@Override
	public IModel readFromResultSet(ResultSet rs) throws SQLException {{
		{0}Model model = null;

			model = new {0}Model();

// read fields
{4}		

		return model;
	}}

	@Override
	public PreparedStatement genSelectStatement(Connection dbConn, IModel query)
			throws SQLException {{
		String sql = ""SELECT * FROM sp_{5}_select({6});"";
		NamedParameterPreparedStatement st = new NamedParameterPreparedStatement(
				dbConn, sql);

        {0}Model obj = ({0}Model )query;
		
// set parameters
{7}

		return st.getPreparedStatement();
	}}

	@Override
	public PreparedStatement genSelectListStatement(Connection dbConn,
			IModelExt query) throws SQLException {{
		String sql = ""SELECT * FROM sp_{5}_selectList(:limit, :offset, {8})"";

		NamedParameterPreparedStatement st = new NamedParameterPreparedStatement(
				dbConn, sql);

		{0}ModelExt obj = ({0}ModelExt) query;

		if (obj.getLimit() != Integer.MIN_VALUE) {{
			st.setObject(""limit"", obj.getLimit());
		}}  else {{
			st.setObject(""limit"", Integer.MAX_VALUE);
		}}
		if (obj.getOffset() != Integer.MIN_VALUE) {{
			st.setObject(""offset"", obj.getOffset());
		}}  else {{
			st.setObject(""offset"", 0);
		}}

// set parameters
{9}	

		return st.getPreparedStatement();
	}}

	@Override
	public PreparedStatement genInsertStatement(Connection dbConn, IModel model)
			throws SQLException {{
		String sql = ""SELECT sp_{5}_insert({10})"";
		NamedParameterPreparedStatement st = new NamedParameterPreparedStatement(
				dbConn, sql);

		{0}Model obj = ({0}Model) model;

		// set parameters here
{11}	

		return st.getPreparedStatement();
	}}

	@Override
	public PreparedStatement genUpdateStatement(Connection dbConn, IModel model)
			throws SQLException {{
		String sql = ""SELECT sp_{5}_update({12})"";

		NamedParameterPreparedStatement st = new NamedParameterPreparedStatement(
				dbConn, sql);

		{0}Model obj = ({0}Model) model;

		// set parameters here
{13}
	
		return st.getPreparedStatement();
	}}

	@Override
	public PreparedStatement genDeleteStatement(Connection dbConn, IModel query)
			throws SQLException {{
		String sql = ""select sp_{5}_delete({6});"";
		NamedParameterPreparedStatement st = new NamedParameterPreparedStatement(
				dbConn, sql);

        {0}Model obj = ({0}Model) query;

		// set primary keys
{7}

		return st.getPreparedStatement();
	}}

	@Override
	public PreparedStatement genCountStatement(Connection dbConn,
			IModelExt input) throws SQLException {{
		String sql = ""SELECT sp_{5}_count({8})"";

		NamedParameterPreparedStatement st = new NamedParameterPreparedStatement(
				dbConn, sql);

		{0}ModelExt obj = ({0}ModelExt) input;

		// set parameters here
{9}
	
		return st.getPreparedStatement();
	}}

/*
	private String fillHtmlTemplate(String template) {{
		String html = template
				{14}
				;
		return html;
	}}
*/

/*
	private String fillHtmlTemplate(String template) {{
		String html = template
				{16}
				;
		return html;
	}}
*/

}}", javaName, errMsgs, variables, getset, readResultSet, dbName,
   selectParams, setSelectParams, selectListParams, setSelectListParams, insertParams, setInsertParams,
   updateParams, setUpdateParams, fillTemplateDBName, fieldNames, fillTemplateJavaName);

            #endregion

            return java;
        }

        private static string GenModelFieldNames(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {
                sb.AppendFormat(@"		public static String {0} = ""{0}"";", GenJavaName(field.Name));
                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenModelFillTemplateDbName(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {

                if (field.DataType == PostgreSqlDataType.Serial || field.DataType == PostgreSqlDataType.Int) {

                    sb.AppendFormat(@".replace("":{0}"", Integer.toString(_{0}))", field.Name);

                } else if (field.DataType == PostgreSqlDataType.Numeric) {

                    sb.AppendFormat(@".replace("":{0}"", StringUtil.toString(_{0}))", field.Name);

                } else if (field.DataType == PostgreSqlDataType.TimeStamp) {

                    sb.AppendFormat(@".replace("":{0}"", StringUtil.toDateTimeString(_{0}))", field.Name);

                } else if (field.DataType == PostgreSqlDataType.Bool) {

                    sb.AppendFormat(@".replace("":{0}"", HtmlUtil.genHtml(_is{0}Set, _{0}))", field.Name);

                } else {

                    sb.AppendFormat(@".replace("":{0}"", HtmlUtil.genHtml(_{0}))", field.Name);
                }

                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenModelFillTemplateJavaName(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {

                if (field.DataType == PostgreSqlDataType.Serial || field.DataType == PostgreSqlDataType.Int) {

                    sb.AppendFormat(@".replace("":{0}"", Integer.toString(_{0}))", field.JavaName);

                } else if (field.DataType == PostgreSqlDataType.Numeric) {

                    sb.AppendFormat(@".replace("":{0}"", StringUtil.toString(_{0}))", field.JavaName);

                } else if (field.DataType == PostgreSqlDataType.TimeStamp) {

                    sb.AppendFormat(@".replace("":{0}"", StringUtil.toDateTimeString(_{0}))", field.JavaName);

                } else if (field.DataType == PostgreSqlDataType.Bool) {

                    sb.AppendFormat(@".replace("":{0}"", HtmlUtil.genHtml(_is{0}Set, _{0}))", field.JavaName);

                } else {

                    sb.AppendFormat(@".replace("":{0}"", HtmlUtil.genHtml(_{0}))", field.JavaName);
                }

                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenModelPrimaryKeys(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("int[] ids = new int[{0}];", table.PrimaryKeys.Count);
            sb.AppendLine();
            for (int i = 0; i < table.PrimaryKeys.Count; i++) {
                PostgreSqlField field = table.PrimaryKeys[i];
                sb.AppendFormat("ids[{0}] = get{1}();", i, field.JavaName);
                sb.AppendLine();
            }

            sb.AppendFormat("return ids;");
            return sb.ToString();
        }

        private static string GenModelSetUpdateParams(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.UpdateFields) {
                if (field.DataType == PostgreSqlDataType.Bool) {
                    sb.AppendFormat(@"		if (obj.get{0}Set()) {{
			st.setObject(""{1}"", obj.get{0}());
		}} else {{
			st.setObject(""{1}"", null);
}}", GenJavaName(field.Name), field.Name);
                } else {
                    sb.AppendFormat(@"if (obj.get{0}() != {1}) {{
			st.setObject(""{2}"", obj.get{0}());
		}} else {{
			st.setObject(""{2}"", null);
}}", GenJavaName(field.Name), field.JavaDefaultValue, field.Name);
                }

                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenModelUpdateParams(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < table.UpdateFields.Count; i++) {
                PostgreSqlField field = table.UpdateFields[i];
                if (i > 0) {
                    sb.Append(",");
                }
                sb.AppendFormat(":{0}", field.Name);
            }
            return sb.ToString();
        }

        private static string GenModelSetInsertParams(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.InsertFields) {
                if (field.DataType == PostgreSqlDataType.Bool) {
                    sb.AppendFormat(@"		if (obj.get{0}Set()) {{
			st.setObject(""{1}"", obj.get{0}());
		}} else {{
			st.setObject(""{1}"", null);
}}", GenJavaName(field.Name), field.Name);
                } else {
                    sb.AppendFormat(@"if (obj.get{0}() != {1}) {{
			st.setObject(""{2}"", obj.get{0}());
		}} else {{
			st.setObject(""{2}"", null);
}}", GenJavaName(field.Name), field.JavaDefaultValue, field.Name);
                }

                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenModelInsertParam(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < table.InsertFields.Count; i++) {
                PostgreSqlField field = table.InsertFields[i];
                if (i > 0) {
                    sb.Append(",");
                }
                sb.AppendFormat(":{0}", field.Name);
            }
            return sb.ToString();
        }

        private static string GenModelSetSelectListParam(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {
                if (field.DataType == PostgreSqlDataType.TimeStamp) {
                    sb.AppendFormat(@"if (obj.get{0}From() != null) {{
			st.setObject(""{1}From"", obj.get{0}From());
		}} else {{
			st.setObject(""{1}From"", null);
		}}
		if (obj.get{0}To() != null) {{
			st.setObject(""{1}To"", obj.get{0}To());
		}} else {{
            st.setObject(""{1}To"", null);
}}", GenJavaName(field.Name), field.Name);
                } else if (field.DataType == PostgreSqlDataType.Bool) {
                    sb.AppendFormat(@"		if (obj.get{0}Set()) {{
			st.setObject(""{1}"", obj.get{0}());
		}} else {{
            st.setObject(""{1}"", null);
}}", GenJavaName(field.Name), field.Name);
                } else {
                    sb.AppendFormat(@"if (obj.get{0}() != {1}) {{
			st.setObject(""{2}"", obj.get{0}());
		}} else {{
            st.setObject(""{2}"", null);
}}", GenJavaName(field.Name), field.JavaDefaultValue, field.Name);
                }

                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenModelSelectListParam(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < table.Fields.Count; i++) {
                PostgreSqlField field = table.Fields[i];
                if (i > 0) {
                    sb.Append(",");
                }
                if (field.DataType == PostgreSqlDataType.TimeStamp) {
                    sb.AppendFormat(":{0}From,:{0}To", field.Name);
                } else {
                    sb.AppendFormat(":{0}", field.Name);
                }

            }
            return sb.ToString();
        }

        private static string GenModelSetSelectParam(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < table.PrimaryKeys.Count; i++) {
                PostgreSqlField field = table.PrimaryKeys[i];
                sb.AppendFormat(@"st.setObject(""{0}"", obj.get{1}());", field.Name, field.JavaName);
                sb.AppendLine();
            }
            return sb.ToString();
        }

        public static string GenModelErrMsgs(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {
                sb.AppendFormat(@"		public String {0} = """";", GenJavaName(field.Name));
                sb.AppendLine();
            }
            return sb.ToString();
        }

        public static string GenModelVariablesJava(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {
                sb.AppendFormat(@"private {0} _{1} = {2};", field.JavaType, field.Name, field.JavaDefaultValue);
                sb.AppendLine();
                if (field.DataType == PostgreSqlDataType.Bool) {
                    sb.AppendFormat(@"private boolean _is{0}Set = false;", field.Name);
                    sb.AppendLine();
                }
            }
            return sb.ToString();
        }

        private static string GenModelGetSet(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {
                sb.AppendFormat(@"public {0} get{1}() {{
		return _{2};
	}}", field.JavaType, GenJavaName(field.Name), field.Name);
                sb.AppendLine();

                if (field.DataType == PostgreSqlDataType.Bool) {
                    sb.AppendFormat(@"public void set{0}({1} value) {{
		_{2} = value;
		_is{2}Set = true;
	}}", GenJavaName(field.Name), field.JavaType, field.Name);
                    sb.AppendLine();
                    sb.AppendFormat(@"public boolean get{0}Set() {{
		return _is{1}Set;
	}}", GenJavaName(field.Name), field.Name);
                } else {
                    sb.AppendFormat(@"public void set{0}({1} value) {{
		_{2} = value;
	}}", field.JavaName, field.JavaType, field.Name);
                }
                sb.AppendLine();

                // ToString
                if (field.DataType == PostgreSqlDataType.Bool) {
                    sb.AppendFormat(@"	public String get{0}ToString() {{
		if (_is{1}Set) {{
			return HtmlUtil.genHtml(_{1});
		}} else {{
			return """";
		}}
	}}", field.JavaName, field.Name);
                } else if (field.DataType == PostgreSqlDataType.Character) {
                    sb.AppendFormat(@"public String get{0}ToString() {{
		if (_{1} != null) {{
			return HtmlUtil.genHtml(_{1});
		}} else {{
			return """";
		}}
	}}", field.JavaName, field.Name);
                } else if (field.DataType == PostgreSqlDataType.Int) {
                    sb.AppendFormat(@"public String get{0}ToString() {{
		if (_{1} == Integer.MIN_VALUE
				|| _{1} == Integer.MAX_VALUE) {{
			return """";
		}} else {{
			return Integer.toString(_{1});
		}}
	}}", field.JavaName, field.Name);
                } else if (field.DataType == PostgreSqlDataType.Numeric) {
                    sb.AppendFormat(@"	public String get{0}ToString() {{
		if (_{1} == Double.MIN_VALUE || _{1} == Double.MAX_VALUE) {{
			return """";
		}} else {{
			return StringUtil.toString(_{1});
		}}
	}}", field.JavaName, field.Name);
                } else if (field.DataType == PostgreSqlDataType.Serial) {
                    sb.AppendFormat(@"public String get{0}ToString() {{
		if (_{1} == Integer.MIN_VALUE
				|| _{1} == Integer.MAX_VALUE) {{
			return """";
		}} else {{
			return Integer.toString(_{1});
		}}
	}}", field.JavaName, field.Name);
                } else if (field.DataType == PostgreSqlDataType.TimeStamp) {
                    sb.AppendFormat(@"public String get{0}ToString() {{
		if (_{1} != null) {{
			return StringUtil.toDateTimeString(_{1});
		}} else {{
			return """";
		}}
	}}", field.JavaName, field.Name);
                }

                sb.AppendLine();
            }

            return sb.ToString();
        }

        private static string GenModelReadResultSet(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {
                if (field.DataType == PostgreSqlDataType.TimeStamp) {
                    sb.AppendFormat(@"{{
				Date value = rs.getTimestamp(""{0}"");
				if (!rs.wasNull()) {{
					model.set{1}(value);
				}}
			}}", field.Name, field.JavaName);
                } else {
                    sb.AppendFormat(@"{{
				{0} value = rs.get{1}(""{2}"");
				if (!rs.wasNull()) {{
					model.set{3}(value);
				}}
			}}", field.JavaType, GenJavaName(field.JavaType), field.Name, GenJavaName(field.Name));
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenModelSelectParam(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < table.PrimaryKeys.Count; i++) {
                PostgreSqlField field = table.PrimaryKeys[i];
                if (i > 0) {
                    sb.Append(",");
                }
                sb.AppendFormat(":{0}", field.Name);
            }
            return sb.ToString();
        }

        #endregion

        #region model ext

        public static string GenInputJava(PostgreSqlTable table) {
            string javaName = GenJavaName(table.Name);
            string variables = GenInputVariables(table);
            string getset = GenInputGetSet(table);

            string java = string.Format(@"package model;

import java.util.*;

public class {0}ModelExt extends {0}Model implements IModelExt {{

	private int _limit = Integer.MIN_VALUE;

	public int getLimit() {{
		return _limit;
	}}

	public void setLimit(int value) {{
		_limit = value;
	}}

	private int _offset = Integer.MIN_VALUE;

	public int getOffset() {{
		return _offset;
	}}

	public void setOffset(int offset) {{
		_offset = offset;
	}}

	private int _orderBy = Integer.MIN_VALUE;

	public int getOrderBy() {{
		return _orderBy;
	}}

	public void setOrderBy(int orderBy) {{
		_orderBy = orderBy;
	}}

	private Date _createdFrom = null;
	private Date _createdTo = null;

	private Date _updatedFrom = null;
	private Date _updatedTo = null;

	private Date _deletion_timeFrom = null;
	private Date _deletion_timeTo = null;

    {1}

	public Date getCreatedFrom() {{
		return _createdFrom;
	}}

	public void setCreatedFrom(Date createdFrom) {{
		_createdFrom = createdFrom;
	}}

	public Date getCreatedTo() {{
		return _createdTo;
	}}

	public void setCreatedTo(Date createdTo) {{
		_createdTo = createdTo;
	}}

	public Date getUpdatedFrom() {{
		return _updatedFrom;
	}}

	public void setUpdatedFrom(Date updatedFrom) {{
		_updatedFrom = updatedFrom;
	}}

	public Date getUpdatedTo() {{
		return _updatedTo;
	}}

	public void setUpdatedTo(Date updatedTo) {{
		_updatedTo = updatedTo;
	}}

	public Date getDeletionTimeFrom() {{
		return _deletion_timeFrom;
	}}

	public void setDeletionTimeFrom(Date deletion_timeFrom) {{
		_deletion_timeFrom = deletion_timeFrom;
	}}

	public Date getDeletionTimeTo() {{
		return _deletion_timeTo;
	}}

	public void setDeletionTimeTo(Date deletion_timeTo) {{
		_deletion_timeTo = deletion_timeTo;
	}}

    {2}

}}", javaName, variables, getset);

            return java;
        }

        private static string GenInputGetSet(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {
                if (field.DataType == PostgreSqlDataType.TimeStamp
                    && field.Name != "created" && field.Name != "updated" && field.Name != "deletion_time") {

                    sb.AppendFormat(@"	public Date get{0}From() {{
		return _{1}From;
	}}

	public void set{0}From(Date value) {{
		_{1}From = value;
	}}

	public Date get{0}To() {{
		return _{1}To;
	}}

	public void set{0}To(Date value) {{
		_{1}To = value;
	}}", GenJavaName(field.Name), field.Name);
                }

                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenInputVariables(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.Fields) {
                if (field.DataType == PostgreSqlDataType.TimeStamp
                    && field.Name != "created" && field.Name != "updated" && field.Name != "deletion_time") {
                    sb.AppendFormat(@"	private Date _{0}From = null;
	private Date _{0}To = null;", field.Name);
                }

                sb.AppendLine();
            }
            return sb.ToString();
        }

        #endregion

        #region servlet

        public static string GenServletJava(PostgreSqlTable table) {
            string javaName = GenJavaName(table.Name);
            string javaNameLowerCase = javaName.Substring(0, 1).ToLower() + javaName.Substring(1);

            string urlPatterns = string.Format(@"""/{0}"", ""/{1}"", ""/{0}Servlet"", ""/{1}Servlet""",
                javaName, javaNameLowerCase);

            string idsLength = table.PrimaryKeys.Count.ToString();
            string ids = GenServletIds(table); // 5
            string readInputInsert = GenServletReadInputInsert(table); // 6
            string readInputUpdate = GenServletReadInputUpdate(table);  // 7

            string java = string.Format(@"package servlet;

import java.io.*;
import java.sql.*;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import util.*;
import bll.*;
import model.*;
import dal.*;

/**
 * Servlet implementation class CompanyServlet
 */
@WebServlet(urlPatterns = {{ {0} }}, initParams = {{
        @WebInitParam(name = ""mode"", value = """") }})
public class {1}Servlet extends HttpServlet {{
	private static final long serialVersionUID = 1L;

	/**
	 * Default constructor.
	 */
	public {1}Servlet() {{
		// TODO Auto-generated constructor stub
	}}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {{

		request.setCharacterEncoding(""UTF-8"");
		response.setContentType(""text/html; charset=UTF-8"");

		String mode = request.getParameter(""mode"");

		if (mode.equalsIgnoreCase(""new"")) {{

			if (RightUtil.<{1}Model>checkInsertRight()) {{

				// TODO : MORE CODE HERE

                getServletContext().getRequestDispatcher(""/{2}New.jsp"").forward(request, response);
			}} else {{
				// TODO no right
			}}
		}} else if (mode.equalsIgnoreCase(""edit"")) {{

                {1}Model query = new {1}Model();				
                {4}

				GenericDal<{1}Model, {1}ModelExt> dal = new GenericDal<{1}Model, {1}ModelExt>(
						new {1}Model());

				{1}Model obj = null;
				try {{
					obj = dal.select(query);
				}} catch (SQLException e) {{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}} catch (ClassNotFoundException e) {{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}} catch (Exception e) {{
				// TODO Auto-generated catch block
				e.printStackTrace();
			    }}

				if (obj != null) {{

					// TODO : MORE CODE HERE

                    request.setAttribute(""{2}"", obj);
                    getServletContext().getRequestDispatcher(""/{2}Edit.jsp"").forward(request, response);

				}} else {{
					// TODO : data not found
				}}			
		}} else if (mode.equalsIgnoreCase(""view"")) {{
			
                {1}Model query = new {1}Model();				
                {4}

				GenericDal<{1}Model, {1}ModelExt> dal = new GenericDal<{1}Model, {1}ModelExt>(
						new {1}Model());

				{1}Model obj = null;
				try {{
					obj = dal.select(query);
				}} catch (SQLException e) {{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}} catch (ClassNotFoundException e) {{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}} catch (Exception e) {{
				    // TODO Auto-generated catch block
				    e.printStackTrace();
			    }}

				if (obj != null) {{
					// TODO : LOAD MASTER DATA HERE
					// It's specific code that cannot be generated

                    request.setAttribute(""{2}"", obj);
                    getServletContext().getRequestDispatcher(""/{2}View.jsp"").forward(request, response);

				}} else {{
					// TODO : data not found
				}}			
		}}
	}}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {{

		request.setCharacterEncoding(""UTF-8"");
		response.setContentType(""text/html; charset=UTF-8"");

		String mode = request.getParameter(""mode"");

		if (mode.equalsIgnoreCase(""insert"")) {{
			
				{1}Model obj = new {1}Model();

                {5}				

                obj.setCreator(SessionUtil.getCurrentUserID());

				if (CommonBll.checkInputs(obj)) {{
					GenericDal<{1}Model, {1}ModelExt> dal = new GenericDal<{1}Model, {1}ModelExt>(
							new {1}Model());

					int res = 0;
					try {{
						res = dal.insert(obj);
					}} catch (SQLException e) {{
						// TODO Auto-generated catch block
						e.printStackTrace();
					}} catch (ClassNotFoundException e) {{
						// TODO Auto-generated catch block
						e.printStackTrace();
					}}  catch (Exception e) {{
				    // TODO Auto-generated catch block
				    e.printStackTrace();
			    }}

					if (res > 0) {{
						// TODO : success, redirect to somewhere
					getServletContext().getRequestDispatcher(""/{2}New.jsp"").forward(request, response);
					}} else {{
						// TODO : processing error here
					}}
				}} else {{

					// invalid input

                    request.setAttribute(""{2}"", obj);
                    getServletContext().getRequestDispatcher(""/{2}Input.jsp"").forward(request, response);

				}}		
		}} else if (mode.equalsIgnoreCase(""update"")) {{		

                {1}Model query = new {1}Model();				
                {4}

				GenericDal<{1}Model, {1}ModelExt> dal = new GenericDal<{1}Model, {1}ModelExt>(
						new {1}Model());

				{1}Model obj = null;
				try {{
					obj = dal.select(query);
				}} catch (SQLException e) {{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}} catch (ClassNotFoundException e) {{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}}  catch (Exception e) {{
				    // TODO Auto-generated catch block
				    e.printStackTrace();
			    }}

				if (obj != null) {{

                    {6}				

                    if (CommonBll.checkInputs(obj)) {{	

					    int res = Integer.MIN_VALUE;
					    try {{
						    res = dal.update(obj);
					    }} catch (SQLException e) {{
						    // TODO Auto-generated catch block
						    e.printStackTrace();
					    }} catch (ClassNotFoundException e) {{
						    // TODO Auto-generated catch block
						    e.printStackTrace();
					    }}  catch (Exception e) {{
				    // TODO Auto-generated catch block
				    e.printStackTrace();
			    }}

					    if (res == 0) {{
						    // TODO success
                            getServletContext().getRequestDispatcher(""/{2}New.jsp"").forward(request, response);
					    }} else {{
						    // TODO error
					    }}
                    }} else {{
					request.setAttribute(""{2}"", obj);
					getServletContext().getRequestDispatcher(""/{2}Edit.jsp"").forward(request, response);
				    }}
				}} else {{
					// TODO data not found
				}}			
		}} else if (mode.equalsIgnoreCase(""delete"")) {{		

                {1}Model query = new {1}Model();				
                {4}

				GenericDal<{1}Model, {1}ModelExt> dal = new GenericDal<{1}Model, {1}ModelExt>(
						new {1}Model());

				int res = Integer.MIN_VALUE;
				try {{
					res = dal.delete(query);
				}} catch (SQLException e) {{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}} catch (ClassNotFoundException e) {{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}}  catch (Exception e) {{
				    // TODO Auto-generated catch block
				    e.printStackTrace();
			    }}

				if (res == 0) {{
					// TODO success
                    getServletContext().getRequestDispatcher(""/{2}List.jsp"").forward(request, response);
				}} else {{
					// TODO fail
				}}			
		}}  else if (mode.equalsIgnoreCase(""quickInsert"")) {{
			{1}Model obj = new {1}Model();
			
			// TODO set name here

			GenericDal<{1}Model, {1}ModelExt> dal = new GenericDal<{1}Model, {1}ModelExt>(new {1}Model());

			int res = 0;
			try {{
				res = dal.insert(obj);
			}} catch (ClassNotFoundException e) {{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}} catch (SQLException e) {{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}} catch (Exception e) {{
				    // TODO Auto-generated catch block
				    e.printStackTrace();
			    }}

			// TODO : return value

		}}
	}}

}}", urlPatterns, javaName, javaNameLowerCase, idsLength, ids, readInputInsert, readInputUpdate, table.Name);

            return java;
        }

        private static string GenServletReadInputUpdate(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.InsertFields) {
                if (field.Name != "creator") {
                    if (field.DataType == PostgreSqlDataType.Bool) {
                        sb.AppendFormat(@"String {0} = request.getParameter(""{0}"");
					obj.set{1}({0}.equalsIgnoreCase(""true"")
							|| {0}.equalsIgnoreCase(""1""));", field.Name, GenJavaName(field.Name));
                    } else if (field.DataType == PostgreSqlDataType.TimeStamp) {
                        sb.AppendFormat(@"String {0}_date = request.getParameter(""{0}_date"");
					String {0}_time = request.getParameter(""{0}_time"");
        obj.set{1}(DateTimeUtil.parseDateTime({0}_date, {0}_time));", field.Name, GenJavaName(field.Name));
                    } else if (field.DataType == PostgreSqlDataType.Int || field.DataType == PostgreSqlDataType.Serial) {
                        sb.AppendFormat(@"
					String {0} = request.getParameter(""{0}"");
					if ({0} != null && !{0}.isEmpty()) {{
					    obj.set{1}(Integer.parseInt({0}));
                    }}", field.Name, GenJavaName(field.Name));
                    } else if (field.DataType == PostgreSqlDataType.Character) {
                        sb.AppendFormat(@"String {0} = request.getParameter(""{0}"");
					obj.set{1}({0});", field.Name, GenJavaName(field.Name));
                    } else if (field.DataType == PostgreSqlDataType.Numeric) {
                        sb.AppendFormat(@"String {0} = request.getParameter(""{0}"");
					obj.set{1}(Double.parseDouble({0}));", field.Name, GenJavaName(field.Name));
                    }
                }

                sb.AppendLine();
                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenServletReadInputInsert(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            foreach (PostgreSqlField field in table.InsertFields) {
                if (field.Name != "creator") {
                    if (field.DataType == PostgreSqlDataType.Bool) {
                        // TODO : check NULLABLE case

                        sb.AppendFormat(@"String {0} = request.getParameter(""{0}"");
					obj.set{1}({0}.equalsIgnoreCase(""true"")
							|| {0}.equalsIgnoreCase(""1""));", field.Name, GenJavaName(field.Name));

                    } else if (field.DataType == PostgreSqlDataType.TimeStamp) {
                        sb.AppendFormat(@"
String {0}_date = request.getParameter(""{0}_date"");
    String {0}_time = request.getParameter(""{0}_time"");
    if ({0}_date != null && !{0}_date.isEmpty() && {0}_time != null && !{0}_time.isEmpty()) {{
            obj.set{1}(DateTimeUtil.parseDateTime({0}_date, {0}_time));
}} else {{
    obj.set{1}(DateTimeUtil.parseDate({0}_date));
}}", field.Name, GenJavaName(field.Name));
                    } else if (field.DataType == PostgreSqlDataType.Int || field.DataType == PostgreSqlDataType.Serial) {
                        sb.AppendFormat(@"String {0} = request.getParameter(""{0}"");
                    if ({0} != null && !{0}.isEmpty()) {{
					    obj.set{1}(Integer.parseInt({0}));
                    }}", field.Name, GenJavaName(field.Name));
                    } else if (field.DataType == PostgreSqlDataType.Character) {
                        sb.AppendFormat(@"String {0} = request.getParameter(""{0}"");
					obj.set{1}({0});", field.Name, GenJavaName(field.Name));
                    } else if (field.DataType == PostgreSqlDataType.Numeric) {
                        sb.AppendFormat(@"String {0} = request.getParameter(""{0}"");
                    if ({0} != null && !{0}.isEmpty()) {{
					    obj.set{1}(Double.parseDouble({0}));
                    }}", field.Name, GenJavaName(field.Name));
                    }
                }
                sb.AppendLine();
                sb.AppendLine();
            }
            return sb.ToString();
        }

        private static string GenServletIds(PostgreSqlTable table) {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < table.PrimaryKeys.Count; i++) {
                PostgreSqlField field = table.PrimaryKeys[i];
                sb.AppendFormat(@"String {0} = request.getParameter(""{0}"");
			query.setId(Integer.parseInt({0}));", field.Name, field.JavaName);
                sb.AppendLine();
            }

            return sb.ToString();
        }

        #endregion

        #region Webservice

        public static string GenWebServiceJava(PostgreSqlTable table) {
            string javaName = GenJavaName(table.Name);
            string javaNameLowerCase = javaName.Substring(0, 1).ToLower() + javaName.Substring(1);

            string urlPatterns = string.Format(@"""/{0}"", ""/{1}"", ""/{0}Servlet"", ""/{1}Servlet""",
                javaName, javaNameLowerCase);

            string idsLength = table.PrimaryKeys.Count.ToString();
            string ids = GenServletIds(table); // 5
            string readInputInsert = GenServletReadInputInsert(table); // 6
            string readInputUpdate = GenServletReadInputUpdate(table);  // 7

            string java = string.Format(@"package webservice;

import java.sql.*;
import java.util.*;

import dal.*;
import model.*;

public class {0}WS {{

	private Connection _dbConn = null;

	private Connection getDbConnection() throws SQLException,
			ClassNotFoundException {{
		if (_dbConn == null) {{
			_dbConn = ConnectionProvider.genConnection();
		}}
		return _dbConn;
	}}

	private GenericDal<{0}Model, {0}ModelExt> _dal = null;

	private GenericDal<{0}Model, {0}ModelExt> getDal()
			throws SQLException, ClassNotFoundException {{
		if (_dal == null) {{
			Connection dbConn = getDbConnection();
			_dal = new GenericDal<{0}Model, {0}ModelExt>(
					new {0}Model(), dbConn);
		}}
		return _dal;
	}}

	public {0}Model select{0}(int[] ids) throws SQLException,
			ClassNotFoundException {{
		GenericDal<{0}Model, {0}ModelExt> dal = getDal();
		{0}Model model = ({0}Model) dal.select(ids);
		return model;
	}}

	public {0}Model[] selectList{0}({0}Input input)
			throws SQLException {{
		List<{0}Model> models = _dal.selectList(input);
		{0}Model[] result = ({0}Model[]) models.toArray();
		return result;
	}}

	public int delete{0}(int[] ids) throws SQLException {{
		int result = _dal.delete(ids);
		return result;
	}}

	public int count{0}({0}ModelExt input) throws SQLException {{
		int count = _dal.count(input);
		return count;
	}}

}}", javaName, javaNameLowerCase);

            return java;
        }

        #endregion

    }
}
